﻿Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Collections
Imports extranet.BE
Imports MySql.Data.MySqlClient

Public Class DAQuestao
  Private _strLimite As String = ""
  Private objBD As New BancoDeDados
  Public objQuery As New BEQuery

  Sub New()
  End Sub

  Sub New(ByVal booCondicaoSimilar As Boolean)
    objQuery.Where.CondicaoSimilar = booCondicaoSimilar
  End Sub

  Public Function Cadastra(ByRef objBEQuestao As BEQuestao) As Integer
    Try
      objBD.duplicaAspasSimples(objBEQuestao, GetType(BEQuestao))
      Dim strSQL = "INSERT INTO ext_questoes (enunciado, respostaDissertativa) " & _
                   "VALUES ('" & objBEQuestao.Enunciado & "', " & _
                             IIf(objBEQuestao.EhDissertativa, 1, 0) & ");" & _
                   "SELECT LAST_INSERT_ID();"
      objBD.InicializarConexao()
      objBD.InicializarTransacao()
      Try
        Dim objComando As New MySqlCommand(strSQL, BancoDeDados.ConnectionInstance, BancoDeDados.TransactionInstance)
        objBEQuestao.Id = objComando.ExecuteScalar()

        If objBEQuestao.EhDissertativa Then
          exclueAlternativas(objBEQuestao)
        Else
          adicionaAlternativas(objBEQuestao)
        End If

        objBD.Commit()
      Catch ex As ApplicationException
        objBD.Rollback()
        Throw New ApplicationException("Erro ao executar o comando: " & ex.Message)
      Finally
        objBD.FinalizarConexao()
      End Try
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DAQuestao.Cadastra(): " & ex.Message)
    End Try
    Return objBEQuestao.Id
  End Function

  Public Sub Atualiza(ByVal objBEQuestao As BEQuestao)
    Try
      objBD.duplicaAspasSimples(objBEQuestao, GetType(BEQuestao))
      Dim strSQL = "UPDATE ext_questoes " & _
                   "SET enunciado='" & objBEQuestao.Enunciado & "', " & _
                       "respostaDissertativa=" & IIf(objBEQuestao.EhDissertativa, 1, 0) & " " & _
                   "WHERE id=" & objBEQuestao.Id & ";"
      objBD.InicializarConexao()
      objBD.InicializarTransacao()
      Try
        Dim objComando As New MySqlCommand(strSQL, BancoDeDados.ConnectionInstance, BancoDeDados.TransactionInstance)
        objComando.ExecuteNonQuery()

        If objBEQuestao.EhDissertativa Then
          exclueAlternativas(objBEQuestao)
        Else
          atualizaAlternativas(objBEQuestao)
        End If

        objBD.Commit()
      Catch ex As ApplicationException
        objBD.Rollback()
        Throw New ApplicationException("Erro ao executar o comando: " & ex.Message)
      Finally
        objBD.FinalizarConexao()
      End Try
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DAQuestao.Atualiza(): " & ex.Message)
    End Try
  End Sub

  Private Sub adicionaAlternativas(ByRef objBEQuestao As BEQuestao)
    Dim strSQL As String = ""
    Try
      For Each objBEAlternativa As BEAlternativa In objBEQuestao.Alternativas
        objBD.duplicaAspasSimples(objBEAlternativa, GetType(BEAlternativa))
        strSQL &= "INSERT INTO ext_alternativas_questao (questaoid, texto, alternativacerta) " & _
                  "VALUES ('" & objBEQuestao.Id & "', '" & _
                                objBEAlternativa.Texto & "', '" & _
                                IIf(objBEAlternativa.RespostaCerta, 1, 0) & "');"
      Next
      Dim objComando As New MySqlCommand(strSQL, BancoDeDados.ConnectionInstance, BancoDeDados.TransactionInstance)
      objComando.ExecuteNonQuery()
    Catch ex As ApplicationException
      Throw New ApplicationException("Erro ao executar relacionamento de Alternativas: " & ex.Message)
    End Try
  End Sub

  Private Sub atualizaAlternativas(ByRef objBEQuestao As BEQuestao)
    Dim strSQL As String = ""
    Try
      For Each objBEAlternativa As BEAlternativa In objBEQuestao.Alternativas
        objBD.duplicaAspasSimples(objBEAlternativa, GetType(BEAlternativa))
        If objBEAlternativa.Id > 0 Then
          strSQL &= "UPDATE ext_alternativas_questao " & _
                    "SET questaoid='" & objBEQuestao.Id & "', " & _
                        "texto='" & objBEAlternativa.Texto & "', " & _
                        "alternativacerta=" & IIf(objBEAlternativa.RespostaCerta, 1, 0) & " " & _
                    "WHERE id=" & objBEAlternativa.Id & ";"
        Else
          strSQL &= "INSERT INTO ext_alternativas_questao (questaoid, texto, alternativacerta) " & _
                    "VALUES ('" & objBEQuestao.Id & "', '" & _
                                  objBEAlternativa.Texto & "', '" & _
                                  IIf(objBEAlternativa.RespostaCerta, 1, 0) & "');"

        End If
      Next
      Dim objComando As New MySqlCommand(strSQL, BancoDeDados.ConnectionInstance, BancoDeDados.TransactionInstance)
      objComando.ExecuteNonQuery()
    Catch ex As ApplicationException
      Throw New ApplicationException("Erro ao executar atualização de Alternativas: " & ex.Message)
    End Try
  End Sub

  Private Sub exclueAlternativas(ByRef objBEQuestao As BEQuestao)
    Dim strSQL As String = "DELETE FROM ext_alternativas_questao WHERE questaoid=" & objBEQuestao.Id & ";"
    Try
      Dim objComando As New MySqlCommand(strSQL, BancoDeDados.ConnectionInstance, BancoDeDados.TransactionInstance)
      objComando.ExecuteNonQuery()
    Catch ex As ApplicationException
      Throw New ApplicationException("Erro ao executar relacionamento de Alternativas: " & ex.Message)
    End Try
  End Sub

  Public Sub Exclui(ByVal objBEQuestao As BEQuestao)
    Try
      objBD.ExecutaNonQuery("DELETE FROM ext_rel_prova_questoes WHERE questaoid='" & objBEQuestao.Id & "';" & _
                            "DELETE FROM ext_alternativas_questao WHERE questaoid='" & objBEQuestao.Id & "';" & _
                            "DELETE FROM ext_questoes WHERE id='" & objBEQuestao.Id & "';")
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DAQuestao.Exclui(): " & ex.Message)
    End Try
  End Sub

  Public Function Seleciona(ByVal objBEQuestao As BEQuestao) As List(Of BEQuestao)
    Dim objBEQuestaos As List(Of BEQuestao) = New List(Of BEQuestao)
    Try
      objBD.duplicaAspasSimples(objBEQuestao, GetType(BEQuestao))
      objQuery._Select = "SELECT q.*, rpq.ordem FROM ext_questoes q INNER JOIN ext_rel_prova_questoes rpq ON q.id=rpq.questaoid"
      objQuery.Sort.AdicionarCondicaoOrderBy("rpq.ordem", "ASC")
      If objBEQuestao IsNot Nothing Then
        If objBEQuestao.Id > 0 Then objQuery.Where.AdicionarCondicaoWhere("q.id", objBEQuestao.Id, False)
        If objBEQuestao.ProvaId > 0 Then objQuery.Where.AdicionarCondicaoWhere("rpq.provaid", objBEQuestao.ProvaId, False)
        objQuery.Where.AdicionarCondicaoWhere("q.enunciado", objBEQuestao.Enunciado)
      End If
      objBEQuestaos = SelecionaQuestoes()
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DAQuestao.Seleciona(): " & ex.Message)
    End Try
    Return objBEQuestaos
  End Function

  Private Function SelecionaQuestoes() As List(Of BEQuestao)
    Dim objBEQuestaos As List(Of BEQuestao) = New List(Of BEQuestao)
    Dim objBEQuestao As BEQuestao
    Try
      Using objSQLCommand As MySqlCommand = New MySqlCommand(objQuery.Query, BancoDeDados.ConnectionInstance())
        Using objSQLDReader As MySqlDataReader = objSQLCommand.ExecuteReader()
          While objSQLDReader.Read()
            objBEQuestao = New BEQuestao()
            With objBEQuestao
              .Id = objSQLDReader("id")
              .Enunciado = objSQLDReader("enunciado").ToString()
              .EhDissertativa = objSQLDReader("respostaDissertativa")
              If Not IsDBNull(objSQLDReader("ordem")) Then .Ordem = objSQLDReader("ordem")
            End With
            objBEQuestaos.Add(objBEQuestao)
          End While
        End Using
      End Using
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DAQuestao.SelecionaQuestoes(): " & ex.Message)
    End Try
    Return objBEQuestaos
  End Function

  Public Function Seleciona1raQuestaoPendente(ByVal objBECandidato As BECandidato) As IList(Of BEQuestao)
    _strLimite = " LIMIT 0,1"
    Return SelecionaQuestoesPendentes(objBECandidato)
  End Function

  Public Function SelecionaQuestoesPendentes(ByVal objBECandidato As BECandidato) As IList(Of BEQuestao)
    Dim intIdProva As Integer = objBECandidato.VagasAssociadas(0).Prova.Id
    objQuery._Select = "SELECT q.*, pq.ordem FROM ext_questoes q " & _
                       "INNER JOIN ext_rel_prova_questoes pq ON q.id=pq.questaoid AND pq.provaid=" & intIdProva & " " & _
                       "WHERE NOT q.id IN (SELECT questaoid FROM ext_notas_candidato " & _
                                          "WHERE candidatoid=" & objBECandidato.Id & " AND provaid=" & intIdProva & ") " & _
                       "ORDER BY pq.ordem " & _strLimite
    _strLimite = ""
    Return SelecionaQuestoes()
  End Function

  Public Sub ResponderQuestao(ByVal objBECandidato As BECandidato)
    Dim booExiste As Boolean = False
    Try
      Dim objBEProva As BEProva = objBECandidato.VagasAssociadas(0).Prova
      objBD.duplicaAspasSimples(objBEProva.Questoes(0), GetType(BEQuestao))
      Dim strSql As String = "SELECT count(*) FROM ext_notas_candidato WHERE " & _
                             "candidatoid=" & objBECandidato.Id & " AND " & _
                             "provaid=" & objBEProva.Id & " AND " & _
                             "questaoid=" & objBEProva.Questoes(0).Id
      Using objSQLCommand As MySqlCommand = New MySqlCommand(strSql, BancoDeDados.ConnectionInstance())
        Using objSQLDReader As MySqlDataReader = objSQLCommand.ExecuteReader()
          While objSQLDReader.Read()
            booExiste = (objSQLDReader(0) > 0)
          End While
        End Using
      End Using

      If booExiste Then
        strSql = "UPDATE ext_notas_candidato set alternativaid="
      Else
        strSql = "INSERT INTO ext_notas_candidato (candidatoid, provaid, questaoid, alternativaid, respostadissertativa) " & _
                 "VALUES(" & objBECandidato.Id & ", " & objBEProva.Id & ", " & objBEProva.Questoes(0).Id & ", "
      End If
      If objBEProva.Questoes(0).Alternativas Is Nothing OrElse objBEProva.Questoes(0).Alternativas.Count() = 0 Then
        strSql &= "NULL, "
      Else
        strSql &= objBEProva.Questoes(0).Alternativas(0).Id & ", "
      End If
      If booExiste Then strSql &= "respostadissertativa="
      strSql &= "'" & objBEProva.Questoes(0).RespostaDissertativa & "' "
      If booExiste Then
        strSql &= "WHERE candidatoid=" & objBECandidato.Id & _
                   " AND provaid=" & objBEProva.Id & _
                   " AND questaoid=" & objBEProva.Questoes(0).Id
      Else
        strSql &= ")"
      End If
      objBD.ExecutaNonQuery(strSql)

      Dim strTempoDeProva As String = "00:00"
      strSql = "SELECT tempo_prova FROM ext_rel_candidato_vagas " & _
               "WHERE candidatoid=" & objBECandidato.Id & " AND vagaid=" & objBECandidato.VagasAssociadas(0).Id
      Using objSQLCommand As MySqlCommand = New MySqlCommand(strSql, BancoDeDados.ConnectionInstance())
        Using objSQLDReader As MySqlDataReader = objSQLCommand.ExecuteReader()
          While objSQLDReader.Read()
            strTempoDeProva = objSQLDReader(0)
          End While
        End Using
      End Using

      Dim tsTempoDeProva As TimeSpan = TimeSpan.Parse(strTempoDeProva & ":00")
      strSql = "UPDATE ext_rel_candidato_vagas " & _
               "SET tempo_prova='" & objBEProva.Questoes(0).Tempo.Add(tsTempoDeProva.Add(New TimeSpan(0, 1, 0))).ToString().Substring(0, 5) & "' " & _
               "WHERE candidatoid=" & objBECandidato.Id & " AND vagaid=" & objBECandidato.VagasAssociadas(0).Id
      objBD.ExecutaNonQuery(strSql)

    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DAQuestao.ResponderQuestao(): " & ex.Message)
    End Try
  End Sub

  Public Function Seleciona1raQuestaoPendenteAluno(ByVal objBEAluno As BEAluno) As IList(Of BEQuestao)
    _strLimite = " LIMIT 0,1"
    Return SelecionaQuestoesPendentesAluno(objBEAluno)
  End Function

  Public Function SelecionaQuestoesPendentesAluno(ByVal objBEAluno As BEAluno) As IList(Of BEQuestao)
    Dim intIdProva As Integer = objBEAluno.Cursos(0).Prova.Id
    objQuery._Select = "SELECT q.*, pq.ordem FROM ext_questoes q " & _
                       "INNER JOIN ext_rel_prova_questoes pq ON q.id=pq.questaoid AND pq.provaid=" & intIdProva & " " & _
                       "WHERE NOT q.id IN (SELECT questaoid FROM ext_respostas_aluno " & _
                                          "WHERE usuario='" & objBEAluno.Usuario & "' AND provaid=" & intIdProva & ") " & _
                       "ORDER BY pq.ordem " & _strLimite
    _strLimite = ""
    Return SelecionaQuestoes()
  End Function

  Public Sub ResponderQuestaoAluno(ByVal objBEAluno As BEAluno)
    Dim booExiste As Boolean = False
    Try
      Dim objBEProva As BEProva = objBEAluno.Cursos(0).Prova
      objBD.duplicaAspasSimples(objBEProva.Questoes(0), GetType(BEQuestao))
      Dim strSql As String = "SELECT count(*) FROM ext_respostas_aluno WHERE " & _
                             "usuario='" & objBEAluno.Usuario & "' AND " & _
                             "provaid=" & objBEProva.Id & " AND " & _
                             "questaoid=" & objBEProva.Questoes(0).Id
      Using objSQLCommand As MySqlCommand = New MySqlCommand(strSql, BancoDeDados.ConnectionInstance())
        Using objSQLDReader As MySqlDataReader = objSQLCommand.ExecuteReader()
          While objSQLDReader.Read()
            booExiste = (objSQLDReader(0) > 0)
          End While
        End Using
      End Using

      If booExiste Then
        strSql = "UPDATE ext_respostas_aluno set alternativaid="
      Else
        strSql = "INSERT INTO ext_respostas_aluno (usuario, provaid, questaoid, alternativaid, respostadissertativa) " & _
                 "VALUES('" & objBEAluno.Usuario & "', " & objBEProva.Id & ", " & objBEProva.Questoes(0).Id & ", "
      End If
      If objBEProva.Questoes(0).Alternativas Is Nothing OrElse objBEProva.Questoes(0).Alternativas.Count() = 0 Then
        strSql &= "NULL, "
      Else
        strSql &= objBEProva.Questoes(0).Alternativas(0).Id & ", "
      End If
      If booExiste Then strSql &= "respostadissertativa="
      strSql &= "'" & objBEProva.Questoes(0).RespostaDissertativa & "' "
      If booExiste Then
        strSql &= "WHERE usuario='" & objBEAluno.Usuario & "' " & _
                   " AND provaid=" & objBEProva.Id & _
                   " AND questaoid=" & objBEProva.Questoes(0).Id
      Else
        strSql &= ")"
      End If
      objBD.ExecutaNonQuery(strSql)

      Dim strTempoDeProva As String = "00:00"
      strSql = "SELECT tempo_prova FROM ext_rel_alunos_cursos " & _
               "WHERE usuario='" & objBEAluno.Usuario & "' AND idCurso=" & objBEAluno.Cursos(0).Id
      Using objSQLCommand As MySqlCommand = New MySqlCommand(strSql, BancoDeDados.ConnectionInstance())
        Using objSQLDReader As MySqlDataReader = objSQLCommand.ExecuteReader()
          While objSQLDReader.Read()
            strTempoDeProva = objSQLDReader(0)
          End While
        End Using
      End Using

      Dim tsTempoDeProva As TimeSpan = TimeSpan.Parse(strTempoDeProva & ":00")
      strSql = "UPDATE ext_rel_alunos_cursos " & _
               "SET tempo_prova='" & objBEProva.Questoes(0).Tempo.Add(tsTempoDeProva.Add(New TimeSpan(0, 1, 0))).ToString().Substring(0, 5) & "' " & _
               "WHERE usuario='" & objBEAluno.Usuario & "' AND idCurso=" & objBEAluno.Cursos(0).Id
      objBD.ExecutaNonQuery(strSql)

    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DAQuestao.ResponderQuestaoAluno(): " & ex.Message)
    End Try
  End Sub

  Public Function SetStatusRespostaDissertativa(ByVal intIdCandidato As Integer, ByVal intIdProva As Integer, _
                                                ByVal intIdQuestao As Integer, ByVal intStatus As Integer) As Boolean
    Try
      Dim strSql As String = "UPDATE ext_notas_candidato SET respostaDissertativaCerta=" & intStatus & " " & _
                             "WHERE candidatoid=" & intIdCandidato & " AND " & _
                                   "provaid=" & intIdProva & " AND " & _
                                   "questaoid=" & intIdQuestao
      objBD.ExecutaNonQuery(strSql)
      Return True
    Catch ex As Exception
      Throw New ApplicationException("extranet.DA.DAQuestao.SetStatusRespostaDissertativa(): " & ex.Message)
      Return False
    End Try
  End Function

  Public Function SetStatusRespostaDissertativa(ByVal strAluno As String, ByVal intIdProva As Integer, _
                                                ByVal intIdQuestao As Integer, ByVal intStatus As Integer) As Boolean
    Try
      Dim strSql As String = "UPDATE ext_respostas_aluno SET respostaDissertativaCerta=" & intStatus & " " & _
                             "WHERE usuario='" & strAluno & "' AND " & _
                                   "provaid=" & intIdProva & " AND " & _
                                   "questaoid=" & intIdQuestao
      objBD.ExecutaNonQuery(strSql)
      Return True
    Catch ex As Exception
      Throw New ApplicationException("extranet.DA.DAQuestao.SetStatusRespostaDissertativa(): " & ex.Message)
      Return False
    End Try
  End Function

End Class
